import sqlite3
import os
from datetime import datetime
from passlib.context import CryptContext
from config import SQLITE_DATABASE_URL

pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

class SQLiteUtils:
    def __init__(self):
        self.connection = None
        self._create_tables()
    
    def _get_connection(self):
        """获取数据库连接"""
        if not self.connection:
            try:
                # 连接SQLite数据库
                self.connection = sqlite3.connect(
                    SQLITE_DATABASE_URL,
                    check_same_thread=False  # 允许在不同线程中使用连接
                )
                # 设置行工厂，使查询结果可以通过字典访问
                self.connection.row_factory = sqlite3.Row
            except sqlite3.Error as e:
                print(f"SQLite connection error: {e}")
                raise
        return self.connection
    
    def _create_tables(self):
        """创建数据表（如果不存在）"""
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            
            # 创建用户表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password_hash TEXT NOT NULL,
                email TEXT UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """)
            
            conn.commit()
        except sqlite3.Error as e:
            print(f"Error creating tables: {e}")
    
    def verify_password(self, plain_password, hashed_password):
        """验证密码"""
        return pwd_context.verify(plain_password, hashed_password)
    
    def get_password_hash(self, password):
        """获取密码哈希"""
        return pwd_context.hash(password)
    
    def get_user(self, username):
        """根据用户名获取用户信息"""
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
            user = cursor.fetchone()
            if user:
                return dict(user)
            return None
        except sqlite3.Error as e:
            print(f"Error getting user: {e}")
            return None
    
    def create_user(self, username, password, email=None):
        """创建新用户"""
        try:
            password_hash = self.get_password_hash(password)
            conn = self._get_connection()
            cursor = conn.cursor()
            cursor.execute(
                "INSERT INTO users (username, password_hash, email) VALUES (?, ?, ?)",
                (username, password_hash, email)
            )
            conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"Error creating user: {e}")
            return False
    
    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            self.connection = None
